<?php 
require_once dirname(__FILE__) . '/DbUtils.class.php';
require_once dirname(__FILE__) . '/metadata.php';
require_once dirname(__FILE__) . '/config.php';
require_once dirname(__FILE__) . '/includes/MiscUtils.class.php';
session_name(SESSION);
session_start();
if (!isset($_SESSION[CLIENT])) {
  return;
}
$myPdo = DbUtils::createPdoInst();

$start = MiscUtils::getParam('start', 0);
$end = MiscUtils::getParam('end', 0);
$class_id = MiscUtils::getParam('class_id', 0);
$mode = MiscUtils::getParam('mode', 'diploma');

$class = NULL;
$cond_vals = new stdClass();
$cond_vals->c = 't.id = :v1';
$cond_vals->v = array(':v1' => $class_id);
$opts = new stdClass();
$opts->select_expr = 't.*, t1.attr3 AS teacher_name, t1.attr4 AS teacher_email';
$opts->join_expr = 'LEFT JOIN ' . TABLE_NAME_PREFIX_PLUGIN . 'users_users as t1 ON t1.id = t.attr6';
$tmp = DbUtils::get($myPdo, TABLE_NAME_PREFIX_PLUGIN . 'semester_classes', $cond_vals, NULL, NULL, NULL, NULL, $opts)->d;
if (count($tmp) > 0) {
  $class = $tmp[0];
}

if (!$class) {
  die('Class Not Found');
}

$cond_vals = new stdClass();
$cond_vals->c = 't.class_id = :v1 AND ((t.start <= :v2 AND t.end >:v2) OR t.enrolled_semester_id != 0)';
$cond_vals->v = array(':v1' => $class_id, ':v2' => $start);
$opts = new stdClass();
$opts->select_expr = 't.enrolled_program_id, t.enrolled_semester_id, t1.*';
$opts->join_expr = 'LEFT JOIN ' . TABLE_NAME_PREFIX_PLUGIN . 'student_students as t1 ON t1.id = t.student_id';
$students = DbUtils::get($myPdo, TABLE_NAME_PREFIX_PLUGIN . 'student_enrolled_classes', $cond_vals, 't1.given_name', 'ASC', NULL, NULL, $opts)->d;

$totals = array('100');
$sessions = array('1.00');
if ($mode == 'diploma') {
  $totals = array('100', '100', '100', '100');
  $sessions = array('0.25', '0.25', '0.25', '0.25');
}

date_default_timezone_set('Pacific/Auckland');

/** PHPExcel */
require_once dirname(__FILE__) . '/libs/phpexcel/PHPExcel.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

$path = dirname(__FILE__) . '/tmp/';
$file = 'RES_' . $class->teacher_name . '_' . $class->attr2 . ((trim($class->attr8) == '') ? '' : ' ' . $class->attr8 ). '_' . $start . '_' . $end . '.xlsx';

$info = new stdClass();
$info->class_id = $class->id;
$info->semester_id = $class->attr5;
$info->secret = SECRET2;
$info->sessions = count($sessions);
$info->students = count($students);
$info->start = $start;
$info->end = $end;
$info->semester_id = $class->attr5;
// Set properties
$objPHPExcel->getProperties()->setCreator("$class->teacher_name")
               ->setLastModifiedBy("$class->teacher_name")
               ->setTitle($class->attr2 . ((trim($class->attr8) == '') ? '' : ' ' . $class->attr8 ))
               ->setSubject($start . ' to ' . $end)
               ->setDescription(json_encode($info))
               ->setKeywords('')
               ->setCategory("Office 2007 XLSX Document");

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle("$start $end");

$horizontal = 67;
$vertical = 5;

$objPHPExcel->getActiveSheet()->mergeCells('A1:' . chr(count($sessions) + $horizontal) . '1');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'If you ARE NOT sure, CONTACT OUR ADMINISTRATOR.');

$count = 10;
if ($mode != 'diploma') {
  $count = 1;
}

$objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($horizontal - 1) . '3', '[Total Score]');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($horizontal - 1) . '4', '[Assement Ratio]');
for ($i = 0; $i < $count; $i++) {
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($i + $horizontal) . '2', '[Assessment ' . ($i + 1) . ']');
  $objPHPExcel->getActiveSheet()->getColumnDimension(chr($i + $horizontal))->setWidth(15);
  if ($i >= count($totals) || $i >= count($sessions)) {
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($i + $horizontal) . '3', '100');
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($i + $horizontal) . '4', '0.00');
    continue;
  }
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($i + $horizontal) . '3', $totals[$i]);
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($i + $horizontal) . '4', $sessions[$i]);
}
$objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($count + $horizontal) . '2', '[Total]');
$objPHPExcel->getActiveSheet()->getColumnDimension(chr($count + $horizontal))->setWidth(15);
  
foreach ($students as $idx => &$student) {
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($horizontal - 2) . ($idx + $vertical), $student->id . '#' . $student->id_number);
  $objPHPExcel->getActiveSheet()->getColumnDimension(chr($horizontal - 2))->setWidth(15);
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($horizontal - 1) . ($idx + $vertical), $student->given_name . ' ' . $student->family_name . ' (' . $student->preferred_name . ')');
  $objPHPExcel->getActiveSheet()->getColumnDimension(chr($horizontal - 1))->setWidth(20);
  $row = $idx + $vertical;
  $str = '=(';
  for ($i = 0; $i < $count; $i++) {
    if ($i != 0) {
      $str .= '+';
    }
    $str .= '(' . chr($horizontal + $i) . $row . '/' . chr($horizontal + $i) . '3)*100*' . chr($horizontal + $i) . '4';
  }
  $str .= ')';
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($count + $horizontal) . ($idx + $vertical), $str);
  $objPHPExcel->getActiveSheet()->getColumnDimension(chr($count + $horizontal))->setWidth(15);
}

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

$obj = new stdClass();
$obj->track_id = MiscUtils::unique();
$obj->class_id = $class_id;
$obj->date = date('Y-m-d', time());
$obj->start = $start;
$obj->end = $end;
$obj->text = 'Roster is downloaded';
$obj->is_uploaded = 0;
$obj->file = '';
$obj->path = '';
DbUtils::add($myPdo, TABLE_NAME_PREFIX_PLUGIN . 'results_logs', $obj);

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $file . '"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
?>